package mapper;

import entity.Per;
import entity.Rol;
import entity.User;
import mapper.imp.PerImp;
import mapper.imp.RolImp;
import util.DButil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class perMapper implements PerImp {


    @Override
    public int insert(Per per) {
        int num=0;
        try (Connection conn = DButil.getConnection()) {
            String sql="insert into per values (?,?)";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,per.getId());
            ps.setString(2, per.getPermission());
            num=ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return num;
    }

    @Override
    public int delete(int id) {
        int num=0;
        try (Connection conn=DButil.getConnection()){
            String sql="delete from per where id=?";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            num=ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return num;
    }

    @Override
    public int update(Per per) {
        int num=0;
        try(Connection conn=DButil.getConnection()) {
            String sql="update  per set permission=? where id=?";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1, per.getPermission());
            ps.setInt(2,per.getId());
            num=ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return num;
    }

    @Override
    public List<User> selectrol(String permission) {
        List<User> list=new ArrayList<>();
        try (Connection conn= DButil.getConnection()){
            String sql="SELECT u.name FROM per p,rol r,user u,u_r ur,r_p rp\n" +
                    "WHERE u.id=ur.uid\n" +
                    "  AND r.id=ur.uid\n" +
                    "  AND r.id=rp.rid\n" +
                    "  AND p.id=rp.pid\n" +
                    "  AND p.permission=?";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,permission);
            ResultSet rs=ps.executeQuery();
            while (rs.next()){
                User user=new User(rs.getInt("id"),rs.getString("name"));
                list.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Rol> selectper(String permission) {
        List<Rol> list=new ArrayList<>();
        try (Connection conn= DButil.getConnection()){
            String sql="SELECT r.role FROM rol r,per p,r_p rp WHERE r.id=rp.rid AND p.id=rp.pid  AND p.permission=";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,permission);
            ResultSet rs=ps.executeQuery();
            while (rs.next()){
                Rol rol=new Rol(rs.getInt("id"),rs.getString("role"));
                list.add(rol);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }


}
